
CREATE PROCEDURE getProductsFromCategory (@name VARCHAR(50))
AS
BEGIN
	select n.name, p.name, c.name, m.name, pr.fixed_price, pr.fixed_quantity from Products as pr
	inner join Producers as p
	on pr.id_producer = p.id_producer
	inner join Names as n
	on n.id_name = pr.id_name
	inner join Measures as m
	on m.id_measure = pr.id_measure
	inner join Categories as c
	on c.id_category = pr.id_category
	where c.name like @name
END 
GO 

CREATE PROCEDURE getProducts
AS
BEGIN
	select n.name, p.name, c.name, m.name, pr.fixed_price, pr.fixed_quantity from Products as pr
	inner join Producers as p
	on pr.id_producer = p.id_producer
	inner join Names as n
	on n.id_name = pr.id_name
	inner join Measures as m
	on m.id_measure = pr.id_measure
	inner join Categories as c
	on c.id_category = pr.id_category
END 
GO 


CREATE PROCEDURE getProductsByName(@name VARCHAR(50),@producerName VARCHAR(50))
AS
BEGIN
	select n.name, p.name from Products as pr
	inner join Names as n
	on n.id_name = pr.id_name
	inner join Producers as p
	on p.id_producer = pr.id_producer
	where p.name = @producerName and n.name = @name
END
GO

CREATE PROCEDURE deleteProductByName(@name VARCHAR(50),@producerName VARCHAR(50))
AS
BEGIN
	delete from Products
	WHERE id_name NOT IN
    (
        select pr.id_name from Products as pr
		inner join Names as n
		on n.id_name = pr.id_name
		inner join Producers as p
		on p.id_producer = pr.id_producer
		where p.name <> @producerName or n.name <> @name
    )
END
GO   


CREATE PROCEDURE getProduct(@name VARCHAR(50),@producerName VARCHAR(50))
AS
BEGIN
    select n.name, p.name, c.name, m.name, pr.fixed_price, pr.fixed_quantity from Products as pr
	inner join Producers as p
	on pr.id_producer = p.id_producer
	inner join Names as n
	on n.id_name = pr.id_name
	inner join Measures as m
	on m.id_measure = pr.id_measure
	inner join Categories as c
	on c.id_category = pr.id_category
	where p.name like @producerName and n.name like @name
END
GO   



CREATE PROCEDURE getProducer (@producer VARCHAR(50)  )
AS
BEGIN
	select p.id_producer from Producers as p
	where p.name like @producer
END 
GO 

CREATE PROCEDURE getCategory(@category VARCHAR(50)  )
AS
BEGIN
	select c.id_category from Categories as c
	where c.name like @category
END 
GO 

CREATE PROCEDURE getMeasure(@measure VARCHAR(50)  )
AS
BEGIN
	select m.id_measure from Measures as m
	where m.name like @measure
END 
GO 

CREATE PROCEDURE getName(@name VARCHAR(50)  )
AS
BEGIN
	select n.id_name  from Names as n
	where n.name like @name
END 
GO 

CREATE PROCEDURE getProductId(@name VARCHAR(50),@producerName VARCHAR(50))
AS
BEGIN
    select pr.id_product from Products as pr
	inner join Producers as p
	on pr.id_producer = p.id_producer
	inner join Names as n
	on n.id_name = pr.id_name
	where p.name like @producerName and n.name like @name
END
GO   

CREATE PROCEDURE getProductCategory(@name VARCHAR(50))
AS
BEGIN
    select c.name from Categories as c
    inner join Products as pr
    on pr.id_category = c.id_category
	inner join Names as n
	on n.id_name = pr.id_name
	where n.name like @name
END
GO   

CREATE PROCEDURE getProductMeasure(@name VARCHAR(50))
AS
BEGIN
    select m.name from Measures as m
    inner join Products as pr
    on pr.id_measure = m.id_measure
	inner join Names as n
	on n.id_name = pr.id_name
	where n.name like @name
END
GO   

CREATE PROCEDURE getAllProducts
AS
BEGIN
    select n.name, p.name from Names as n
    inner join Products as pr
    on pr.id_name = n.id_name
    inner join Producers as p
    on p.id_producer = pr.id_producer
END
GO   

CREATE PROCEDURE getAllNotes(@date DATE)
AS
BEGIN
    select n.id_note, n.topic, n.userlogin from Notes as n
    WHERE @date like n.id_date
END
GO   

CREATE PROCEDURE getAllProductsForNote (@id_note INT)
AS
BEGIN
    select n.name,pr.name, c.name,m.name,np.price,np.quantity  from Products as p
    inner join Names as n
    on n.id_name = p.id_name
    inner join Producers as pr
    on p.id_producer = pr.id_producer
    inner join Categories as c
    on p.id_category = c.id_category
    inner join Measures as m
    on p.id_measure = m.id_measure
    inner join Notes_Products as np
    on np.id_product = p.id_product 
    where @id_note like np.id_note 
END
GO   


CREATE PROCEDURE getNoteId(@date DATE, @topic VARCHAR(255), @user VARCHAR(50))
AS
BEGIN
    select n.id_note from Notes as n
    WHERE @date like n.id_date and @topic like n.topic and @user like n.userlogin
END
GO   

CREATE PROCEDURE getPrice(@name VARCHAR(50), @producer VARCHAR(50))
AS
BEGIN
    select p.fixed_price from Products as p
    inner join Names as n
    on n.id_name = p.id_name
    inner join Producers as pr
    on pr.id_producer = p.id_producer
	where n.name like @name and pr.name like @producer
END
GO   

CREATE PROCEDURE getQuantity(@name VARCHAR(50), @producer VARCHAR(50))
AS
BEGIN
    select p.fixed_quantity from Products as p
    inner join Names as n
    on n.id_name = p.id_name
    inner join Producers as pr
    on pr.id_producer = p.id_producer
	where n.name like @name and pr.name like @producer
END
GO   

CREATE PROCEDURE getRealPrice(@id_note INT, @name VARCHAR(50), @producer VARCHAR(50))
AS
BEGIN
    select np.price from Notes_Products as np
    inner join Products as p
    on p.id_product = np.id_product
    inner join Names as n
    on n.id_name = p.id_name
    inner join Producers as pr
    on pr.id_producer = p.id_producer
	where n.name like @name and pr.name like @producer and @id_note like np.id_note
END
GO   

CREATE PROCEDURE getRealQuantity(@id_note INT, @name VARCHAR(50), @producer VARCHAR(50))
AS
BEGIN
    select np.quantity from Notes_Products as np
    inner join Products as p
    on p.id_product = np.id_product
    inner join Names as n
    on n.id_name = p.id_name
    inner join Producers as pr
    on pr.id_producer = p.id_producer
	where n.name like @name and pr.name like @producer and @id_note like np.id_note
END
GO   


CREATE PROCEDURE changeProductQuantity(@id_note INT, @id_product INT, @quantity FLOAT)
AS
BEGIN
	IF(@id_product NOT IN (select id_product from Notes_Products where @id_note like id_note))
	BEGIN
		RAISERROR ('Brak produktu o podanym ID' , 1 , 1)
		RETURN
	END
	UPDATE Notes_Products SET quantity = @quantity WHERE id_note like @id_note and id_product like @id_product
END 
GO 

CREATE PROCEDURE changeProductPrice(@id_note INT, @id_product INT, @price FLOAT)
AS
BEGIN
	IF(@id_product NOT IN (select id_product from Notes_Products where @id_note like id_note))
	BEGIN
		RAISERROR ('Brak produktu o podanym ID' , 1 , 1)
		RETURN
	END
	UPDATE Notes_Products SET price = @price WHERE id_note like @id_note and id_product like @id_product
END 
GO 

CREATE PROCEDURE getTopic(@id_note INT)
AS
BEGIN
    select n.topic from Notes as n
	where n.id_note like @id_note
END
GO   

CREATE PROCEDURE checkUser(@login VARCHAR(50), @pass VARCHAR(50))
AS
BEGIN
	IF @login NOT IN (select userlogin from Users where @login like userlogin)
	BEGIN
		RAISERROR ('Brak Uzytkownika' , 1 , 1)
		RETURN
	END
    select passwrd from Users 
    where @login like userlogin and passwrd like @pass
END
GO   


CREATE PROCEDURE getUser(@login VARCHAR(50))
AS
BEGIN
	select userlogin from Users 
	where @login like userlogin
END
GO   


CREATE FUNCTION getAmountForCategory (@name VARCHAR(50)) RETURNS FLOAT 
AS 
BEGIN 
    RETURN (select SUM(p.fixed_price) from  Products as p 
    inner join Categories as c 
    on p.id_category = c.id_category 
    where c.name like @name) 
END
go

CREATE FUNCTION getAmountForCategoryForData (@name VARCHAR(50), @startDate DATE, @endDate DATE) RETURNS FLOAT 
AS 
BEGIN 
    RETURN (select SUM(pn.price) from  Notes_Products as pn
    inner join Products as p 
    on pn.id_product = p.id_product
    inner join Categories as c 
    on p.id_category = c.id_category 
    inner join Notes as n
    on pn.id_note = n.id_note
    where c.name like @name AND n.id_date BETWEEN @startDate AND @endDate ) 
END
go

CREATE FUNCTION getAmountForCategoryForMonth (@name VARCHAR(50), @date DATE) RETURNS FLOAT 
AS 
BEGIN 
    RETURN (select SUM(pn.price) from  Notes_Products as pn
    inner join Products as p 
    on pn.id_product = p.id_product
    inner join Categories as c 
    on p.id_category = c.id_category 
    inner join Notes as n
    on pn.id_note = n.id_note
    where c.name like @name AND YEAR(n.id_date) = YEAR(@date) AND MONTH(n.id_date) = MONTH(@date)  ) 
END
go


CREATE FUNCTION getAmountWeryf (@name VARCHAR(50), @startDate DATE, @endDate DATE) RETURNS FLOAT 
AS 
BEGIN 
    RETURN (select SUM(o.price) from  OldNotes as o
    inner join Products as p 
    on o.id_product = p.id_product
    inner join Categories as c 
    on p.id_category = c.id_category 
    inner join Notes as n
    on o.id_note = n.id_note
    where c.name like @name AND n.id_date BETWEEN @startDate AND @endDate ) 
END
go


CREATE FUNCTION getAmountForProduct (@name VARCHAR(50), @producer VARCHAR(50), @startDate DATE, @endDate DATE) RETURNS FLOAT 
AS 
BEGIN 
    RETURN (select SUM(pn.price) from  Notes_Products as pn
    inner join Products as p 
    on pn.id_product = p.id_product
    inner join Notes as n
    on pn.id_note = n.id_note
    inner join Names as na
    on na.id_name = p.id_name
    inner join Producers as pr
    on pr.id_producer = p.id_producer
    where na.name like @name AND pr.name like @producer AND n.id_date BETWEEN @startDate AND @endDate ) 
END
go

CREATE FUNCTION getWeryfForProduct (@name VARCHAR(50), @producer VARCHAR(50), @startDate DATE, @endDate DATE) RETURNS FLOAT 
AS 
BEGIN 
    RETURN (select SUM(o.price) from  OldNotes as o
    inner join Products as p 
    on o.id_product = p.id_product
    inner join Notes as n
    on o.id_note = n.id_note
    inner join Names as na
    on na.id_name = p.id_name
    inner join Producers as pr
    on pr.id_producer = p.id_producer
    where na.name like @name AND pr.name like @producer AND n.id_date BETWEEN @startDate AND @endDate ) 
END
go
